package com.ipworkbench.common.db;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.ipworkbench.common.util.IPWException;


/**
 * Dao 基类，使用DbUtils操作数据
 * 
 * @author zhangfaheng
 *
 * @date May 15, 2010
 */
public class BaseDao extends BaseDaoSupport{
	
	private QueryRunner queryRunner;
       
    /**  
     * 执行非查询sql语句（添加、修改、删除），需在业务层启用事务  
     * 
     * @param sql sql语句  
     * 
     * @param params 参数数组
     *   
     * @return 受影响的行数  
     */  
    public int update(String sql, Object... params) throws IPWException{     
        int affectedRows = 0;   
        queryRunner = new QueryRunner();
         
        try {   
        	Connection conn = getTranConn();
            if (params == null) {   
                affectedRows = queryRunner.update(conn,sql);   
            } else {   
                affectedRows = queryRunner.update(conn,sql, params);   
            }   
        } catch (SQLException e) {   
            throw new IPWException(e);   
        } 
        return affectedRows;   
    }   
    
       
    /**  
     * 执行批量非查询sql语句（添加、修改、删除），需在业务层启用事务
     * 
     * @param sql sql语句  
     * 
     * @param params 二维参数数组
     *   
     * @return 受影响的行数的数组  
     */  
    public  int[] batchUpdate(String sql, Object[][] params) throws IPWException{   
        queryRunner = new QueryRunner();   
        int[] affectedRows = new int[0];   
        try {   
        	Connection conn = getTranConn();
        	
            affectedRows = queryRunner.batch(conn,sql, params);   
        } catch (SQLException e) {   
        	throw new IPWException(e);   
        }   
        return affectedRows;   
    } 
       
    /**  
     * 执行查询，将每行的结果保存到一个Map对象中，然后将所有Map对象保存到List中  
     * 
     * @param sql sql语句
     *   
     * @param params 参数数组
     *   
     * @return 查询结果  
     */  
    @SuppressWarnings("unchecked")   
    public List<Map<String, Object>> find(String sql, Object... params) throws IPWException{   
    	queryRunner = new QueryRunner();   
        List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
        Connection conn = null;
        try {   
        	
        	 conn = getConn();
            if (params == null) {   
                list = (List<Map<String, Object>>) queryRunner.query(conn,sql, new MapListHandler());   
            } else {   
                list = (List<Map<String, Object>>) queryRunner.query(conn,sql, new MapListHandler(), params);   
            }   
        } catch (SQLException e) {   
        	throw new IPWException(e);  
        }finally{
        	DbUtils.closeQuietly(conn);
        }     
        return list;   
    }   
       
   
       
    /**  
     * 执行查询，将每行的结果保存到Bean中，然后将所有Bean保存到List中  
     * 
     * @param entityClass 类名  
     * @param sql sql语句  
     * @param params 参数数组  
     * @return 查询结果  
     */  
    @SuppressWarnings("unchecked")   
    public <T> List<T> find(Class<T> entityClass, String sql, Object... params)throws IPWException{
    	
    	queryRunner = new QueryRunner();   
        List<T> list = new ArrayList<T>();
        Connection conn = null;
        
        try {   
        	
        	conn = getConn();
            if (params == null) {   
                list = (List<T>) queryRunner.query(conn,sql, new BeanListHandler(entityClass));   
            } else {   
                list = (List<T>) queryRunner.query(conn,sql, new BeanListHandler(entityClass), params);   
            }   
        } catch (SQLException e) {   
        	throw new IPWException(e);    
        }finally{
        	DbUtils.closeQuietly(conn);
        }    
        return list;   
    }   
       
   
       
    /**  
     * 查询出结果集中的第一条记录，并封装成对象  
     * 
     * @param entityClass 类名  
     * @param sql sql语句  
     * @param params 参数数组  
     * @return 对象  
     */  
    @SuppressWarnings("unchecked")   
    public <T> T findFirst(Class<T> entityClass, String sql, Object... params) throws IPWException{
    	
    	queryRunner = new QueryRunner();   
        Object object = null;
        Connection conn = null;
        
        try {   
        	
        	conn = getConn();
            if (params == null || params.length == 0) {   
                object = queryRunner.query(conn,sql, new BeanHandler(entityClass));   
            } else {   
                object = queryRunner.query(conn,sql, new BeanHandler(entityClass), params);   
            }   
        } catch (SQLException e) {   
        	throw new IPWException(e);   
        }finally{
        	DbUtils.closeQuietly(conn);
        }    
        return (T) object;   
    }   
       
  
       
    /**  
     * 查询出结果集中的第一条记录，并封装成Map对象  
     * @param sql sql语句  
     * @param params 参数数组  
     * @return 封装为Map的对象  
     */  
    @SuppressWarnings("unchecked")   
    public Map<String, Object> findFirst(String sql, Object... params) throws IPWException{
    	
    	queryRunner = new QueryRunner();   
        Map<String, Object> map = null;
        Connection conn = null; 
        	
        try {   
        	conn = getConn();
            if (params == null) {   
                map = (Map<String, Object>) queryRunner.query(conn,sql, new MapHandler());   
            } else {   
                map = (Map<String, Object>) queryRunner.query(conn,sql, new MapHandler(), params);   
            }   
        } catch (SQLException e) {   
        	throw new IPWException(e);
        }finally{
        	DbUtils.closeQuietly(conn);
        }     
        return map;   
    }   
       
  
       
    /**  
     * 查询某一条记录，并将指定列的数据转换为Object  
     * 
     * @param sql sql语句  
     * @param columnName 列名  
     * @param params 参数数组  
     * @return 结果对象  
     */  
    public Object findBy(String sql, String columnName, Object... params) throws IPWException{
    	
    	queryRunner = new QueryRunner();   
        Object object = null;
        Connection conn = null;
        
        try {   
        	conn = getConn();
            if (params == null) {   
                object = queryRunner.query(conn,sql, new ScalarHandler(columnName));   
            } else {   
                object = queryRunner.query(conn,sql, new ScalarHandler(columnName), params);   
            }   
        } catch (SQLException e) {   
        	throw new IPWException(e);   
        }finally{
        	DbUtils.closeQuietly(conn);
        }     
        return object;   
    }   
       
    
       
    /**  
     * 查询某一条记录，并将指定列的数据转换为Object 
     *  
     * @param sql sql语句  
     * @param columnIndex 列索引  
     * @param params 参数数组  
     * @return 结果对象  
     */  
    public Object findBy(String sql, int columnIndex, Object... params) throws IPWException{
    	
    	queryRunner = new QueryRunner();   
        Object object = null;
        Connection conn = null;
        
        try {   
        	conn = getConn();
            if (params == null) {   
                object = queryRunner.query(conn,sql, new ScalarHandler(columnIndex));   
            } else {   
                object = queryRunner.query(conn,sql, new ScalarHandler(columnIndex), params);   
            }   
        } catch (SQLException e) {   
        	throw new IPWException(e);
        }finally{
        	DbUtils.closeQuietly(conn);
        }     
        return object;   
    }   

	
}
